# src/models/comment_schema.py
from sqlalchemy import Column, String, Text, DateTime, Integer, Float, Date,ForeignKey, TIMESTAMP,Boolean
from sqlalchemy.dialects.mysql import JSON  # MySQL 特有类型
from .base import Base
from sqlalchemy.orm import relationship
from enum import Enum

class PlatformType(str, Enum):
    youtube = "youtube"
    facebook = "facebook"
    discord = "discord"


class RawComment(Base):
    __tablename__ = 'd_raw_comments'
    id = Column(Integer, primary_key=True, autoincrement=True)
    author = Column(String(100))
    user_id = Column(String(100))  # 用户唯一ID（跨平台）
    video_id = Column(String(50), nullable=False)  # 对应视频/帖子/频道ID
    source_type = Column(String(50))  # 如 video/post/channel/group
    platform = Column(String(50))  # 来源平台：youtube/facebook/discord
    published_at = Column(TIMESTAMP)  # 发布时间
    like_count = Column(Integer)  # 点赞数
    reactions = Column(JSON)  # 所有反应信息（如 {"like": 10, "love": 5}）
    comment_text = Column(Text, nullable=False)  # 评论文本
    raw_data = Column(JSON)  # 原始API返回数据，方便调试与回溯

    # 外键 & 关系
    video_source_id = Column(Integer, ForeignKey('d_video_sources.id'))  # 关联视频源
    video_source = relationship("VideoSource", back_populates="comments")

    # 父评论关系
    parent_id = Column(String(255))  # 父评论ID（如主评论）
    parent_text = Column(Text)  # 父评论内容（用于上下文分析）
    is_analyzed = Column(Boolean, default=False)  # 是否已分析
    top_comment_id = Column(String(100), unique=True, index=True)  # 平台原始评论ID
    is_deleted = Column(Boolean, default=False)  # 是否被删除（软删除）
    deleted_at = Column(TIMESTAMP)  # 删除时间（如有）

     # ✅ 新增字段：分析时间戳
    analyzed_at = Column(DateTime, default=None)  # 分析时间，NULL 表示未分析

    def to_dict(self):
        return {
            "id": self.id,
            "video_source_id": self.video_source_id,
            "comment_text": self.comment_text,
            "is_analyzed": self.is_analyzed
        }
    def __repr__(self):
        return f"<RawComment(author='{self.author}', text='{self.comment_text[:20]}...')>"
    

class RVVideo(Base):
    __tablename__ = 'rv_videos'
    video_id = Column(String(50), primary_key=True)
    title = Column(String(255))
    view_count = Column(Integer)

class AnalyzedComment(Base):
    __tablename__ = 'd_analyzed_comment'

    id  = Column(Integer, primary_key=True)
    video_source_id = Column(
        Integer, 
        ForeignKey('d_video_sources.id'),
        nullable=False  # ✅ 明确禁止 NULL
    )
    original_text = Column(Text)
    translated_text = Column(Text)
    primary_label = Column(String(100))
    secondary_labels = Column(JSON)  
    confidence_score = Column(Float)

    # 新增字段
    like_count = Column(Integer, default=0)  # 同步自原始评论的点赞数

    # 新增字段
    platform = Column(String(50))          # 平台来源
    parent_id = Column(String(255))        # 父评论ID
    parent_text = Column(Text)             # 父评论文本
    top_comment_id = Column(String(255))   # 顶级评论ID


    def to_dict(self):
        return {
            "video_source_id": self.video_source_id,
            "original_text": self.original_text,
            "translated_text": self.translated_text,
            "primary_label": self.primary_label,
            "secondary_labels": self.secondary_labels,
            "confidence_score": float(self.confidence_score) if self.confidence_score is not None else None,
            "like_count": self.like_count
        }


class CommentTag(Base):
    __tablename__ = 'd_comment_tag'

    id = Column(Integer, primary_key=True)
    comment_id = Column(Integer)
    tag_id = Column(Integer)
    created_at = Column(TIMESTAMP)

class VideoSource(Base):
    __tablename__ = 'd_video_sources'
    id = Column(Integer, primary_key=True)
    video_url = Column(String(255), nullable=False, unique=True)
    video_id = Column(String(50), nullable=False)
    title = Column(Text)
    author = Column(String(100))
    brand = Column(String(100))
    views = Column(Integer)# 👈 单位为“万”
    comment_count = Column(Integer)
    publish_date = Column(Date)
    
    # 新增字段
    platform = Column(String(50), default='youtube')  # 支持 facebook/discord
    source_type = Column(String(50))  # 如 post/channel/video
    group_id = Column(String(100))  # 如 Facebook Group ID
    channel_name = Column(String(100))  # 如 Discord 频道名称
    # 新增字段映射
    subtitle_processed = Column(Integer, default=0)  # 0 或 1 已处理字幕字段
    comments = relationship("RawComment", back_populates="video_source")
    # 新增字段映射
    comment_process_status = Column(String(50))  # 评论处理状态字段


class TagDict(Base):
    __tablename__ = 'd_tag_dict'

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    parent_id = Column(Integer)
    level = Column(Integer)  # 1: 一级标签；2: 二级标签
    created_at = Column(DateTime)

    def __repr__(self):
        return f"<TagDict(id={self.id}, name='{self.name}', parent_id={self.parent_id}, level={self.level})>"